import os
import re
import pandas as pd

invalid_char = "([^\’!\"#$%&\'()*+,/:;<=>?@，。?、…【】《》？“”‘’！["u"\\]^`{|}~])*$"


class ImportExcel():
    def __init__(self, excel_file):
        self.model = '所属模块'
        self.child_model = '子模块'
        self.auto_test_case_num = '自动化用例编号'
        self.case_title = '用例标题'
        self.prodect_name = '产品名称'
        self.q_version = '是否Q版'
        self.q_version_case = None
        self.invalid_model = []
        self.invalid_child_model = []
        self.invalid_row = []
        self.model_dict = []
        self.case_dict = []
        self.full_case_dict = []
        self.full_model_case = None
        self.all_qversion = []
        self.model_list = []
        self.child_model_list = []
        self.read_case(excel_file)

    def read_case(self, excel_file):
        if os.path.isfile(excel_file) and os.path.exists(excel_file):
            self.df = pd.read_excel(excel_file)
            self.get_invalid_model()
            self.get_invalid_child_model()
            self.get_model()
            self.get_case_dict()

    def get_invalid_model(self):
        model_t = self.df[self.model].to_list()
        for i in model_t:
            for j in str(i):
                if not re.match(invalid_char, j):
                    self.invalid_model.append(i)
        print("命名错误所属模块：", self.invalid_model)

    def get_invalid_child_model(self):
        child_model_t = self.df[self.child_model].to_list()
        for x in child_model_t:
            for y in str(x):
                if not re.match(invalid_char, y):
                    self.invalid_child_model.append(x)
        print("命名错误子模块：", self.invalid_child_model)

    def get_invalid_model_row(self):
        invalid_model_row = []
        invalid_child_model_row = []
        for i in set(self.invalid_model):
            row = self.df[self.df[self.model].isin([i])].index.tolist()
            invalid_model_row += row

        for i in set(self.invalid_child_model):
            row = self.df[self.df[self.child_model].isin([i])].index.tolist()
            invalid_child_model_row += row

        self.invalid_row = invalid_model_row + invalid_child_model_row
        self.invalid_row = set(
            map(lambda x: x+2, self.invalid_row))  # 用例表格无index需+2
        print("命名错误行：", sorted(self.invalid_row))
        return list(sorted(self.invalid_row))

    def get_model(self):
        full_model_case = []
        other_model_case = []
        if self.invalid_row == []:
            q_version_row = self.df[self.q_version].to_list()
            q_version = []
            for i in range(0, len(q_version_row)):
                if q_version_row[i] == '是':
                    q_version.append(i)
            case_title_row = self.df[self.case_title].to_list()
            case_title_col = []
            for i in range(0, len(case_title_row)):
                if pd.isnull(self.df.loc[i, self.case_title]):
                    pass
                else:
                    case_title_col.append(i)
            # self.q_version_case = list(
            #     set(q_version).intersection(set(case_title_col)))
            self.q_version_case = q_version
            print("q版本用例行号:", self.q_version_case, len(self.q_version_case))
            for idx, row in self.df.iterrows():
                if pd.isnull(self.df.loc[idx, self.child_model]):
                    if idx+2 in self.q_version_case:
                        model_name = str(self.df.loc[idx, self.model])
                        other_model_case.append(model_name)
                else:
                    if idx+2 in self.q_version_case:
                        model_name = str(
                            self.df.loc[idx, self.model])+'-' + str(self.df.loc[idx, self.child_model])
                        full_model_case.append(model_name)
            self.full_model_case = list(set(full_model_case))
            self.other_model_case = list(set(other_model_case))
            print('===============================================')
            print("所有所属模块和子模块都存在的用例：", self.full_model_case)
            print("所有只有所属模块用例：", self.other_model_case)
            print('===============================================')

    def get_case_dict(self):
        for i in set(self.full_model_case):
            t = []
            t.clear()
            for row in self.df.itertuples(index=True):
                child_model = getattr(row, self.child_model)
                if not pd.isnull(child_model):
                    key = getattr(row, self.model) + '-' + child_model
                    if i == key and row[0] in self.q_version_case:
                        t.append(row[0])
            self.model_list.append({i: t})

        for i in set(self.other_model_case):
            t = []
            t.clear()
            for row in self.df.itertuples(index=True):
                child_model = getattr(row, self.child_model)
                if pd.isnull(child_model):
                    key = getattr(row, self.model)
                    if i == key and row[0] in self.q_version_case:
                        t.append(row[0])
            self.child_model_list.append({i: t})
        self.all_qversion = self.child_model_list + self.model_list

# case = ImportExcel("/home/lz/桌面/qq.xls")
# case.get_case_dict()
# print(len(case.q_version_case))
